﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;

namespace QLTV_V2.DataAccess
{
    class ConnectData
    {
        private SqlConnection conn;
        private SqlDataAdapter dataAp;
        private DataTable dataTable;
        public byte cf = 0;
        //Tạo constructor gọi kết nối khi new lớp ConnectData
        public ConnectData()
        {
            Connect();
        }
        //Kết nối
        public void Connect()
        {
            string strConn = @"Data Source=" + ConfigurationManager.AppSettings["Server"].ToString()
                + ";Initial Catalog=" + ConfigurationManager.AppSettings["Database"].ToString()
                + ";User ID=" + ConfigurationManager.AppSettings["Username"].ToString()
                + ";Password=" + ConfigurationManager.AppSettings["Password"].ToString() + ";Integrated Security=True";
            try
            {
                conn = new SqlConnection(strConn);
                conn.Open();             
            }
            catch
            {
                try
                {
                    string con = System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
                    System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(con);
                    cnn.Open();
                }
                catch
                {
                    frm.frmCauHinh ch = new frm.frmCauHinh();
                    ch.ShowDialog();
                    cf = ch.cf;
                    ch.Dispose();
                    if (cf == 1)
                        Application.Restart();
                    else
                        Application.Exit();
                }
            }
        }
        //Hàm lấy dữ liệu Datatable từ câu truy vấn truyền vào
        public DataTable GetDataTable(string sql)
        {
            //Tạo dataApdapter, thực hiện câu lệnh sql
            dataAp = new SqlDataAdapter(sql, conn);
            //Đỗ dữ liệu vào DataTable
            dataTable = new DataTable();
            dataAp.Fill(dataTable);
            return dataTable;
        }
        //Hàm thực hiện câu truy vấn insert, update, delete
        public bool ExecuteQuery(string sql)
        {
            int numRecord = 0;
            SqlCommand cmd = new SqlCommand(sql, conn);
            numRecord = cmd.ExecuteNonQuery();
            if ((numRecord > 0))
            {
                return true;
            }
            return false;
        }
        //Thuc hiên cậu lệnh sql sử dụng ExecuteScalar
        public bool ExecuteScalars(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (cmd.ExecuteScalar() != null)
                return true;
            return false;
        }
        public AutoCompleteStringCollection AutoCompelete(string tableName, string column)
        {
            string sql = "select " + column + " from " + tableName + " order by "+column;
            AutoCompleteStringCollection auto = new AutoCompleteStringCollection();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader;
            reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                { 
                    auto.Add(reader[column].ToString());
                }
            }
            return auto;
        }
        public string[] getArray(string fielName, string tableName, string fileNameDK, string dk, int kt)
        {

            string sql;
            if (kt == 1)
                sql = "select " + fielName + " from " + tableName + " where " + fileNameDK + " = '" + dk + "'";
            else
                sql = "select " + fielName + " from " + tableName + "";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader dr1 = cmd.ExecuteReader();
            
            int i = 0;
            string[] arrayString1 = new string[200];
            while (dr1.Read())
            {
                arrayString1[i] = dr1[fielName].ToString();
                i++;
            }
            dr1.Close();
            int count = i;
            i = 0;
            string[] arrayString = new string[count];
            for (i = 0; i < count; i++)
            {
                arrayString[i] = arrayString1[i];
            }
            return arrayString;
        }
    }
}
